from flask import Blueprint, render_template, session, redirect, url_for, request
import pymysql
import datetime
from werkzeug.security import generate_password_hash, check_password_hash
from decimal import Decimal
from flask import jsonify
import random
import time
from flask import flash
import re


customer_bp = Blueprint('customer', __name__, url_prefix='/customer')

DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '200412',
    'database': 'keshe',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

verification_cache = {}
def get_db_connection():
    return pymysql.connect(**DB_CONFIG)


@customer_bp.route('/dashboard', endpoint='dashboard')
def customer_dashboard():
    if session.get('role') != 'customer':
        return redirect('/login')
    return render_template('customer_dashboard.html')


@customer_bp.route('/balance')
def balance():
    if session.get('role') != 'customer':
        return redirect('/login')

    customer_id = session.get('user')
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM cardInfo WHERE customerID = %s", (customer_id,))
    my_cards = cursor.fetchall()

    cursor.execute("SELECT * FROM tradeInfo WHERE cardID IN (SELECT cardID FROM cardInfo WHERE customerID = %s)", (customer_id,))
    transactions = cursor.fetchall()

    conn.close()

    total_balance = sum(card.get('balance', 0) for card in my_cards)
    valid_types = {'存入', '支取'}


    for card in my_cards:
        card['openDate'] = card.get('openDate', '未提供')
        card_tx = [tx for tx in transactions if tx.get('cardID') == card['cardID'] and tx.get('tradeType') in valid_types]
        if card_tx:
            card_tx.sort(key=lambda x: x.get('tradeDate', ''), reverse=True)
            card['lastTransactionTime'] = card_tx[0].get('tradeDate', '暂无记录')
        else:
            card['lastTransactionTime'] = '暂无记录'

    return render_template('customer_balance.html', balance=total_balance, cards=my_cards)


@customer_bp.route('/transactions')
def transactions():
    if session.get('role') != 'customer':
        return redirect('/login')

    customer_id = session.get('user')
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM cardInfo WHERE customerID = %s", (customer_id,))
    cards = cursor.fetchall()
    card_ids = [c['cardID'] for c in cards]

    if not card_ids:
        return render_template('customer_transactions.html', transactions=[])

    format_strings = ','.join(['%s'] * len(card_ids))
    cursor.execute(f"SELECT * FROM tradeInfo WHERE cardID IN ({format_strings})", tuple(card_ids))
    customer_transactions = cursor.fetchall()

    conn.close()

    return render_template('customer_transactions.html', transactions=customer_transactions)


@customer_bp.route('/transaction_center')
def transaction_center():
    if session.get('role') != 'customer':
        return redirect('/login')
    return render_template('customer_transaction_center.html')


@customer_bp.route('/deposit', methods=['GET', 'POST'])
def deposit():
    if session.get('role') != 'customer':
        return redirect('/login')

    customer_id = session.get('user')
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM cardInfo WHERE customerID = %s", (customer_id,))
    customer_cards = cursor.fetchall()

    message = ''

    if request.method == 'POST':
        selected_card_id = request.form.get('cardID')
        amount_str = request.form.get('amount')

        cursor.execute("SELECT * FROM cardInfo WHERE cardID = %s", (selected_card_id,))
        card = cursor.fetchone()

        if not card:
            message = '所选银行卡无效'
        elif card.get('IsReportLoss') == '是':
            message = '该卡已挂失，无法存款'
        else:
            try:
                amount = Decimal(amount_str)
                if amount <= 0:
                    message = '金额必须大于0'
                else:
                    new_balance = card['balance'] + amount
                    cursor.execute("UPDATE cardInfo SET balance = %s WHERE cardID = %s",
                                   (new_balance, selected_card_id))
                    # 不插入交易记录，交给触发器
                    conn.commit()
                    message = f"成功存入 {amount} 元！"
            except ValueError:
                message = '请输入有效金额'

    conn.close()
    return render_template('customer_deposit.html', message=message, cards=customer_cards)


@customer_bp.route('/withdraw', methods=['GET', 'POST'])
def withdraw():
    if session.get('role') != 'customer':
        return redirect('/login')

    customer_id = session.get('user')
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM cardInfo WHERE customerID = %s", (customer_id,))
    customer_cards = cursor.fetchall()

    message = ''

    if request.method == 'POST':
        selected_card_id = request.form.get('cardID')
        amount_str = request.form.get('amount')

        cursor.execute("SELECT * FROM cardInfo WHERE cardID = %s", (selected_card_id,))
        card = cursor.fetchone()

        if not card:
            message = '所选银行卡无效'
        elif card.get('IsReportLoss') == '是':
            message = '该卡已挂失，无法取款'
        else:
            try:
                amount = Decimal(amount_str)
                if amount <= 0:
                    message = '金额必须大于0'
                elif amount > card['balance']:
                    message = '余额不足'
                else:
                    new_balance = card['balance'] - amount
                    cursor.execute("UPDATE cardInfo SET balance = %s WHERE cardID = %s",
                                   (new_balance, selected_card_id))
                    # 不插入交易记录，交给触发器
                    conn.commit()
                    message = f"成功取出 {amount} 元！"
            except ValueError:
                message = '请输入有效金额'

    conn.close()
    return render_template('customer_withdraw.html', message=message, cards=customer_cards)


@customer_bp.route('/transfer', methods=['GET', 'POST'])
def transfer():
    if session.get('role') != 'customer':
        return redirect('/login')

    customer_id = session.get('user')
    conn = get_db_connection()
    cursor = conn.cursor()

    message = ''
    success = False

    try:
        # 查询用户所有银行卡
        cursor.execute("SELECT * FROM cardInfo WHERE customerID = %s", (customer_id,))
        customer_cards = cursor.fetchall()

        if request.method == 'POST':
            from_card_id = request.form.get('from_card')
            to_card_id = request.form.get('to_card')
            amount_str = request.form.get('amount')

            # 验证付款卡和收款卡是否存在
            cursor.execute("SELECT * FROM cardInfo WHERE cardID = %s", (from_card_id,))
            from_card = cursor.fetchone()

            cursor.execute("SELECT * FROM cardInfo WHERE cardID = %s", (to_card_id,))
            to_card = cursor.fetchone()

            if not from_card or not to_card:
                message = '付款卡或收款卡无效'
            elif from_card_id == to_card_id:
                message = '不能给自己转账'
            elif from_card.get('IsReportLoss') == '是':
                message = '付款卡已挂失，无法转账'
            elif to_card.get('IsReportLoss') == '是':
                message = '收款卡已挂失，无法接收转账'
            else:
                try:
                    # 统一使用Decimal处理金额
                    from decimal import Decimal
                    amount = Decimal(amount_str)

                    # 获取Decimal类型的余额
                    current_balance = Decimal(str(from_card['balance']))

                    # ========== 金额验证 ==========
                    if amount <= Decimal('0'):
                        message = '转账金额必须大于0'
                    elif amount > Decimal('1000000'):
                        message = '单笔转账金额不能超过1,000,000元'
                    elif current_balance < amount:
                        message = f'余额不足，当前余额:{current_balance:.2f}元'
                    elif (current_balance - amount) < Decimal('1'):
                        message = '转账后余额必须保持至少1元'
                    # ========== 验证结束 ==========
                    else:
                        # 调用存储过程（确保传递Decimal类型）
                        cursor.callproc('transferMoney', (
                            from_card_id,
                            to_card_id,
                            float(amount),  # 存储过程通常能处理float
                            ''
                        ))

                        # 获取存储过程输出
                        cursor.execute('SELECT @_transferMoney_3')
                        result = cursor.fetchone()
                        result_msg = result[0] if result else None

                        conn.commit()

                        if result_msg is None or result_msg == 0:
                            message = '转账成功'
                            success = True
                        else:
                            # 处理存储过程返回的消息
                            if '❌' in str(result_msg):
                                message = f'转账失败：{str(result_msg).split("❌")[-1].strip()}'
                            else:
                                message = '转账成功'
                                success = True

                except ValueError:
                    message = '请输入有效的金额'
                except Exception as e:
                    conn.rollback()
                    if str(e) == '0':
                        message = '转账成功'
                        success = True
                    else:
                        message = f'转账失败：{str(e)}'

        return render_template('customer_transfer.html',
                               cards=customer_cards,
                               message=message,
                               success=success)

    finally:
        conn.close()

@customer_bp.route('/report_lost', methods=['GET', 'POST'])
def report_lost():
    if session.get('role') != 'customer':
        return redirect('/login')

    customer_id = session.get('user')
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM cardInfo WHERE customerID = %s", (customer_id,))
    my_cards = cursor.fetchall()

    if request.method == 'POST':
        card_id = request.form['cardID']
        action = request.form.get('action')

        if action == 'report':
            cursor.execute("UPDATE cardInfo SET IsReportLoss = '是' WHERE cardID = %s", (card_id,))
        else:
            cursor.execute("UPDATE cardInfo SET IsReportLoss = '否' WHERE cardID = %s", (card_id,))

        conn.commit()
        return redirect(url_for('customer.report_lost'))

    conn.close()
    return render_template('customer_report_lost.html', cards=my_cards)


@customer_bp.route('/change_password', methods=['GET', 'POST'])
def change_password():
    if session.get('role') != 'customer':
        return redirect('/login')

    customer_id = session.get('user')
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM cardInfo WHERE customerID = %s", (customer_id,))
    my_cards = cursor.fetchall()

    message = ''
    success = False

    if request.method == 'POST':
        card_id = request.form['cardID']
        old_password = request.form['old_password']
        new_password = request.form['new_password']
        confirm_password = request.form['confirm_password']

        cursor.execute("SELECT * FROM cardInfo WHERE cardID = %s", (card_id,))
        card = cursor.fetchone()

        if not card:
            message = '找不到所选银行卡'
        elif card.get('pass') != old_password:
            message = '原密码错误'
        elif new_password != confirm_password:
            message = '两次新密码输入不一致'
        elif not re.fullmatch(r'\d{6}', new_password):
            message = '密码必须是6位数字'
        elif new_password == old_password:
            message = '新密码不能与旧密码相同'
        else:
            try:
                cursor.execute("UPDATE cardInfo SET pass = %s WHERE cardID = %s", (new_password, card_id))
                conn.commit()
                message = '密码修改成功！'
                success = True
            except Exception as e:
                message = f'密码修改失败：{e}'

    conn.close()
    return render_template('customer_change_password.html', cards=my_cards, message=message, success=success)



# 验证验证码是否正确且未过期
def check_verification_code(phone, code):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 假设有表 verification_codes 存储手机号、验证码和过期时间
            sql = """
            SELECT expire_time FROM verification_codes
            WHERE phone = %s AND code = %s
            ORDER BY expire_time DESC LIMIT 1
            """
            cursor.execute(sql, (phone, code))
            result = cursor.fetchone()
            if not result:
                return False
            expire_time = result['expire_time']
            return expire_time > datetime.datetime.now()
    finally:
        conn.close()


def check_verification_code(phone, code):
    record = verification_cache.get(phone)
    if not record:
        return False
    if time.time() > record['expire']:
        return False
    return record['code'] == code


# 验证客户姓名、身份证号、手机号 和银行卡号是否对应匹配


def get_user_cards(customerID):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = "SELECT cardID FROM cardInfo WHERE customerID = %s"
            cursor.execute(sql, (customerID,))
            cards = cursor.fetchall()
            return cards  # 返回 [{'cardID': 'xxx'}, ...]
    finally:
        conn.close()

def check_card_belongs_to_customer(cardID, name, pid, phone):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 先查这张卡对应的客户ID
            cursor.execute("SELECT customerID FROM cardInfo WHERE cardID = %s", (cardID,))
            card = cursor.fetchone()
            if not card:
                return False
            customerID = card['customerID']

            # 再根据客户ID查姓名、身份证号、手机号是否都匹配
            sql = """
            SELECT * FROM userInfo
            WHERE customerID = %s AND customerName = %s AND PID = %s AND telephone = %s
            """
            cursor.execute(sql, (customerID, name, pid, phone))
            user = cursor.fetchone()
            return user is not None
    finally:
        conn.close()

@customer_bp.route('/send_verification_code', methods=['POST'])
def send_verification_code():
    try:
        data = request.get_json()
        name = data.get('name')
        pid = data.get('pid')
        phone = data.get('phone')
        cardID = data.get('cardID')

        # 检查必填项
        if not all([name, pid, phone, cardID]):
            return jsonify({'success': False, 'message': '卡号、姓名、身份证号和手机号不能为空'})

        # 连接数据库校验客户和银行卡信息是否匹配
        conn = pymysql.connect(**DB_CONFIG)
        with conn.cursor() as cursor:
            sql = """
                SELECT * FROM userInfo u
                JOIN cardInfo c ON u.customerID = c.customerID
                WHERE u.customerName=%s AND u.PID=%s AND u.telephone=%s AND c.cardID=%s
            """
            cursor.execute(sql, (name, pid, phone, cardID))
            result = cursor.fetchone()
        conn.close()

        if not result:
            return jsonify({'success': False, 'message': '客户信息与银行卡号不匹配，请核实'})

        # 60秒内不能重复发送
        current_time = time.time()
        if phone in verification_cache:
            last_sent = verification_cache[phone].get('send_time', 0)
            if current_time - last_sent < 60:
                remain = int(60 - (current_time - last_sent))
                return jsonify({'success': False, 'message': f'请稍候 {remain} 秒后再试'})

        # 生成验证码，5分钟过期
        code = str(random.randint(100000, 999999))
        expire_time = current_time + 300  # 5分钟有效期

        # 缓存验证码，覆盖旧的
        verification_cache[phone] = {
            'code': code,
            'expire': expire_time,
            'send_time': current_time
        }

        print(f"[验证码已发送] phone={phone}, code={code}")

        return jsonify({
            'success': True,
            'code': code,
            'expire_seconds': 300,
            'message': '验证码发送成功'
        })

    except Exception as e:
        print("验证码发送失败 ❌：", str(e))
        return jsonify({'success': False, 'message': '服务器错误，请稍后重试'}), 500


def update_card_password(cardID, new_pass):
    new_pass = str(new_pass)  # ✅ 强制转字符串
    if not re.fullmatch(r'\d{6}', new_pass):
        print("密码必须是6位数字")
        return False

    old_pass = get_card_password(cardID)
    if old_pass is None:
        print("卡号不存在")
        return False

    if str(old_pass) == new_pass:
        print("新密码不能与原密码相同")
        return False

    try:
        conn = pymysql.connect(**DB_CONFIG)
        cursor = conn.cursor()
        sql = "UPDATE cardInfo SET pass = %s WHERE cardID = %s"
        cursor.execute(sql, (new_pass, cardID))
        conn.commit()
        conn.close()
        return True
    except Exception as e:
        print("更新密码失败:", e)
        return False

@customer_bp.route('/forget_password_reset', methods=['GET', 'POST'])
def forget_password_reset():
    customerID = session.get('user')
    user_cards = get_user_cards(customerID)

    if request.method == 'POST':
        cardID = request.form.get('cardID')
        name = request.form.get('name')
        pid = request.form.get('pid')
        phone = request.form.get('phone')
        code = request.form.get('code')
        new_password = request.form.get('new_password')
        confirm_password = request.form.get('confirm_password')

        if not all([cardID, name, pid, phone, code, new_password, confirm_password]):
            flash('请填写所有必填项', 'error')
            return render_template('customer_forget_password_reset.html', user_cards=user_cards)

        if new_password != confirm_password:
            flash('两次输入密码不一致', 'error')
            return render_template('customer_forget_password_reset.html', user_cards=user_cards)

        # 新增：密码必须是6位数字
        if not re.fullmatch(r'\d{6}', new_password):
            flash('密码必须是6位数字', 'error')
            return render_template('customer_forget_password_reset.html', user_cards=user_cards)

        # 新增：取旧密码，判断新密码是否与旧密码相同
        old_password = get_card_password(cardID)
        if old_password is None:
            flash('银行卡不存在', 'error')
            return render_template('customer_forget_password_reset.html', user_cards=user_cards)

        if new_password == old_password:
            flash('新密码不能和原密码相同', 'error')
            return render_template('customer_forget_password_reset.html', user_cards=user_cards)

        if not check_verification_code(phone, code):
            flash('验证码错误或已过期', 'error')
            return render_template('customer_forget_password_reset.html', user_cards=user_cards)

        if not check_card_belongs_to_customer(cardID, name, pid, phone):
            flash('客户信息与银行卡号不匹配，请核实', 'error')
            return render_template('customer_forget_password_reset.html', user_cards=user_cards)

        if update_card_password(cardID, new_password):
            flash('密码重置成功，请重新登录', 'success')
            return redirect(url_for('login'))
        else:
            flash('密码重置失败，请稍后重试', 'error')

    return render_template('customer_forget_password_reset.html', user_cards=user_cards)


def get_card_password(cardID):
    try:
        conn = pymysql.connect(**DB_CONFIG)
        with conn.cursor() as cursor:
            cursor.execute("SELECT pass FROM cardInfo WHERE cardID=%s", (cardID,))
            row = cursor.fetchone()
            return row['pass'] if row else None
    finally:
        conn.close()
